﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class ActivityDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();

        public ActivityDAL()
        {

        }
        public DataSet LoadActivity(Nullable<int> Sequence, Nullable<int> JobSiteSeq
            , Nullable<DateTime> ProjectStartDate, Nullable<DateTime> ProjectEndDate)
        {
            DataSet ds = new DataSet();
            dbManager.Open();
            dbManager.CreateParameters(5);
            dbManager.AddParameters(0, "@Sequence", Sequence);
            dbManager.AddParameters(1, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(2, "@ProjectStartDate", ProjectStartDate.HasValue ? ProjectStartDate : SystemInfo._DefaultDateStart);
            dbManager.AddParameters(3, "@ProjectEndDate", ProjectEndDate.HasValue ? ProjectEndDate : SystemInfo._DefaultDateEnd);
            dbManager.AddParameters(4, "@JobSiteSeq", JobSiteSeq);

            string sql = "SELECT a.*, at.ACT_TYPE_DESCR, js.JOB_SITE_NAME, ci.CITY_NAME, ci.COUNTRY_SEQ, co.COUNTRY_NAME"
                        + ", 'รอการพิจารณา ' AS APPROVE"
                        + ", (select count(granted) from hrm_ac_activity_requests ar where ar.act_seq = a.act_seq AND granted not like 0 )AS TOTAL"
                        + ", (select count(granted) from hrm_ac_activity_requests ar where ar.act_seq = a.act_seq )AS CANDIDATE"
                        + ", oper.BGM_PROJ_OPER_NAME, oper.ATTEND_COUNT"
                        + " FROM HRM_AC_ACTIVITYS a"
                        + " LEFT JOIN HRM_AC_ACTIVITY_TYPES at ON a.ACT_TYPE_SEQ = at.ACT_TYPE_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES js ON a.JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                        + " LEFT JOIN BGM_PROJECT_OPERATION oper ON a.BGM_PROJ_OPER_SEQ = oper.BGM_PROJ_OPER_SEQ"
                        + " LEFT JOIN MAS_CITIES ci ON a.CITY_SEQ = ci.CITY_SEQ"
                        + " LEFT JOIN MAS_COUNTRIES co ON ci.COUNTRY_SEQ = co.COUNTRY_SEQ"
                        + " WHERE a.RECORD_STATUS = @ActiveRecord"
                        + (Sequence.HasValue ? " AND a.ACT_SEQ = @Sequence " : "")
                        + (JobSiteSeq.HasValue ? " AND a.JOB_SITE_SEQ = @JobSiteSeq" : "")
                        + " AND (a.DATE_ACTIVITY_START >= @ProjectStartDate OR a.DATE_ACTIVITY_END <= @ProjectEndDate )";

            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dbManager.Dispose();
            return ds;
        }

        public void Insert(Nullable<int> bgm_proj_oper_seq, string act_descr, int act_type_seq, int job_site_seq
            , int act_limit, Nullable<DateTime> date_sign_start, Nullable<DateTime> date_sign_end, DateTime date_app_start, DateTime date_app_end
            , DateTime date_activity_start, DateTime date_activity_end, string time_sign_end
            , int city_seq, string location_text, string remarks, int created_by)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(18);

                dbManager.AddParameters(0, "@act_descr", act_descr);
                dbManager.AddParameters(1, "@act_type_seq", act_type_seq);
                dbManager.AddParameters(2, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(3, "@act_limit", act_limit);
                dbManager.AddParameters(4, "@date_sign_start", date_sign_start);
                dbManager.AddParameters(5, "@date_sign_end", date_sign_end);
                dbManager.AddParameters(6, "@time_sign_end", string.IsNullOrEmpty(time_sign_end) ? null : time_sign_end);
                dbManager.AddParameters(7, "@date_activity_start", date_activity_start);
                dbManager.AddParameters(8, "@date_activity_end", date_activity_end);
                dbManager.AddParameters(9, "@date_app_start", date_app_start);
                dbManager.AddParameters(10, "@date_app_end", date_app_end);
                dbManager.AddParameters(11, "@location_text", location_text);
                dbManager.AddParameters(12, "@city_seq", city_seq);
                dbManager.AddParameters(13, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
                dbManager.AddParameters(14, "@remarks", remarks);
                dbManager.AddParameters(15, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(16, "@created_by", created_by);
                dbManager.AddParameters(17, "@created_when", DateTime.Today);

                strSQL = "INSERT INTO hrm_ac_activitys"
                    + "(act_descr"
                    + ", bgm_proj_oper_seq"
                    + ", act_type_seq"
                    + ", job_site_seq"
                    + ", act_limit"
                    + ", date_sign_start"
                    + ", date_sign_end"
                    + ", time_sign_end"
                    + ", date_activity_start"
                    + ", date_activity_end"
                    + ", date_app_start"
                    + ", date_app_end"
                    + ", location_text"
                    + ", city_seq"
                    + ", remarks"
                    + ", record_status"
                    + ", created_by"
                    + ", created_when)"
                    + " VALUES (@act_descr"
                    + ", @bgm_proj_oper_seq"
                    + ", @act_type_seq"
                    + ", @job_site_seq"
                    + ", @act_limit"
                    + ", @date_sign_start"
                    + ", @date_sign_end"
                    + ", @time_sign_end"
                    + ", @date_activity_start"
                    + ", @date_activity_end"
                    + ", @date_app_start"
                    + ", @date_app_end"
                    + ", @location_text"
                    + ", @city_seq"
                    + ", @remarks"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Update(int act_seq, Nullable<int> bgm_proj_oper_seq, string act_descr, int act_type_seq, int job_site_seq, int act_limit
            , Nullable<DateTime> date_sign_start, Nullable<DateTime> date_sign_end, DateTime date_app_start, DateTime date_app_end
            , DateTime date_activity_start, DateTime date_activity_end, string time_sign_end
            , int city_seq, string location_text, string remarks, int updated_by)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(18);

                dbManager.AddParameters(0, "@act_descr", act_descr);
                dbManager.AddParameters(1, "@act_type_seq", act_type_seq);
                dbManager.AddParameters(2, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(3, "@act_limit", act_limit);
                dbManager.AddParameters(4, "@date_sign_start", date_sign_start);
                dbManager.AddParameters(5, "@date_sign_end", date_sign_end);
                dbManager.AddParameters(6, "@time_sign_end", string.IsNullOrEmpty(time_sign_end) ? null : time_sign_end);
                dbManager.AddParameters(7, "@date_activity_start", date_activity_start);
                dbManager.AddParameters(8, "@date_activity_end", date_activity_end);
                dbManager.AddParameters(9, "@date_app_start", date_app_start);
                dbManager.AddParameters(10, "@date_app_end", date_app_end);
                dbManager.AddParameters(11, "@location_text", location_text);
                dbManager.AddParameters(12, "@city_seq", city_seq);
                dbManager.AddParameters(13, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
                dbManager.AddParameters(14, "@remarks", remarks);
                dbManager.AddParameters(15, "@updated_by", updated_by);
                dbManager.AddParameters(16, "@updated_when", DateTime.Today);
                dbManager.AddParameters(17, "@act_seq", act_seq);

                strSQL = "UPDATE hrm_ac_activitys"
                    + " SET act_descr = @act_descr"
                    + ", bgm_proj_oper_seq = @bgm_proj_oper_seq"
                    + ", act_type_seq = @act_type_seq"
                    + ", job_site_seq = @job_site_seq"
                    + ", act_limit = @act_limit"
                    + ", date_sign_start = @date_sign_start"
                    + ", date_sign_end = @date_sign_end"
                    + ", time_sign_end = @time_sign_end"
                    + ", date_activity_start = @date_activity_start"
                    + ", date_activity_end = @date_activity_end"
                    + ", date_app_start = @date_app_start"
                    + ", date_app_end = @date_app_end"
                    + ", location_text = @location_text"
                    + ", city_seq = @city_seq"
                    + ", remarks = @remarks"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE act_seq = @act_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_AC_ACTIVITYS"
                    + " SET RECORD_STATUS = @RecordStatus"
                    + ", UPDATED_BY = @UpdatedBy"
                    + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE ACT_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
